﻿/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
GO
declare @entity_type_id int;

if not exists(select * from dbo.EntityType where entity_type_uid = '71752005-F6F7-4F37-84CF-07C1869562FE')
begin
	insert into dbo.EntityType (name, entity_type_uid, entity_name_value) values ('Machine', '71752005-F6F7-4F37-84CF-07C1869562FE', 'DnsName');
end

if not exists(select * from dbo.EntityType where entity_type_uid = '0E2DA894-6632-4FAC-924C-9BE48F6206DB')
begin
	insert into dbo.EntityType (name, entity_type_uid, entity_name_value) values ('Cluster', '0E2DA894-6632-4FAC-924C-9BE48F6206DB', 'ClusterName');
end

if not exists(select * from dbo.EntityType where entity_type_uid = 'FF4740CE-DA0C-4FC1-98D3-59854A5EA3DA')
begin
	insert into dbo.EntityType (name, entity_type_uid, entity_name_value, source) values ('Instance', 'FF4740CE-DA0C-4FC1-98D3-59854A5EA3DA', 'InstanceName', 'select @@SERVERNAME as InstanceName, SERVERPROPERTY(N''ProductVersion'') as Version');
	select @entity_type_id = entity_type_id from dbo.EntityType where entity_type_uid = 'FF4740CE-DA0C-4FC1-98D3-59854A5EA3DA';
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'InstanceName', 0);
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'Version', 0);
end

if not exists(select * from dbo.EntityType where entity_type_uid = '9FB2D560-5E41-498B-B184-874620E0C398')
begin
	insert into dbo.EntityType (name, entity_type_uid, parent_uid, entity_name_value, source) values ('Database', '9FB2D560-5E41-498B-B184-874620E0C398', 'FF4740CE-DA0C-4FC1-98D3-59854A5EA3DA', 'DatabaseName', 'select name as DatabaseName, database_id, collation_name, create_date from sys.databases');
	select @entity_type_id = entity_type_id from dbo.EntityType where entity_type_uid = '9FB2D560-5E41-498B-B184-874620E0C398';
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'DatabaseName', 0);
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'database_id', 0);
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'collation_name', 0);
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'create_date', 0);

	insert into dbo.AlertRule (name, [description], cause, resolution, entity_type_property_id, string_warning_value, string_warning_type) values
		('Wrong collation setting','The database has a collation setting set to latin1.','Value of collation is latin1','Change the collation setting',(select top 1 entity_type_property_id from dbo.EntityTypeProperty where name = 'collation_name' and entity_type_id = @entity_type_id),
			'latin1', 2);
end

if not exists(select * from dbo.EntityType where entity_type_uid = 'AB15B654-0604-451A-A413-BCAB884BEE35')
begin
	insert into dbo.EntityType (name, entity_type_uid, parent_uid, entity_name_value, source) values ('Job', 'AB15B654-0604-451A-A413-BCAB884BEE35', 'FF4740CE-DA0C-4FC1-98D3-59854A5EA3DA', 'name', 'select job_id, name, enabled, description from msdb.dbo.sysjobs');
	select @entity_type_id = entity_type_id from dbo.EntityType where entity_type_uid = 'AB15B654-0604-451A-A413-BCAB884BEE35';
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'job_id', 0);
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'name', 0);
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'enabled', 0);
	insert into dbo.EntityTypeProperty (entity_type_id, name, type_value) values (@entity_type_id, 'description', 0);
end

if not exists(select * from dbo.dimPerfMonCounter)
begin
	insert into dbo.dimPerfMonCounter ([perfMonObject], [perfMonCounter], [calcType], [counterInstance]) 
		values ('PhysicalDisk', '% Disk Write Time', 0, '$driveletters');
	insert into dbo.dimPerfMonCounter ([perfMonObject], [perfMonCounter], [calcType], [counterInstance]) 
		values ('SQLServer:Buffer Manager', 'Buffer Cache Hit Ratio', 0, '');
	insert into dbo.dimPerfMonCounter ([perfMonObject], [perfMonCounter], [calcType], [counterInstance]) 
		values ('LogicalDisk', 'Free Megabytes', 0, '$driveletters');
	insert into dbo.dimPerfMonCounter ([perfMonObject], [perfMonCounter], [calcType], [counterInstance]) 
		values ('Memory', 'Pages/sec', 0, '');
	insert into dbo.dimPerfMonCounter ([perfMonObject], [perfMonCounter], [calcType], [counterInstance]) 
		values ('PhysicalDisk', 'Avg. Disk Read Queue Length', 1, '$driveletters');
	insert into dbo.dimPerfMonCounter ([perfMonObject], [perfMonCounter], [calcType], [counterInstance]) 
		values ('PhysicalDisk', 'Avg. Disk Write Queue Length', 1, '$driveletters');
end

if not exists(select * from dbo.DeviationPercent)
begin
SET IDENTITY_INSERT [dbo].[DeviationPercent] ON;
INSERT INTO [dbo].[DeviationPercent]([deviation_Id],[valmin],[valmax])
VALUES
	(0,0,0.1725),
	(1,0.1725,0.370875),
	(2,0.370875,0.59900625),
	(3,0.59900625,0.861357187499999),
	(4,0.861357187499999,1.163060765625),
	(5,1.163060765625,1.51001988046875),
	(6,1.51001988046875,1.90902286253906),
	(7,1.90902286253906,2.36787629191992),
	(8,2.36787629191992,2.89555773570791),
	(9,2.89555773570791,3.50239139606409),
	(10,3.50239139606409,4.20025010547371),
	(11,4.20025010547371,5.00278762129476),
	(12,5.00278762129476,5.92570576448898),
	(13,5.92570576448898,6.98706162916232),
	(14,6.98706162916232,8.20762087353667),
	(15,8.20762087353667,9.61126400456717),
	(16,9.61126400456717,11.2254536052522),
	(17,11.2254536052522,13.0817716460401),
	(18,13.0817716460401,15.2165373929461),
	(19,15.2165373929461,17.671518001888),
	(20,17.671518001888,20.4947457021712),
	(21,20.4947457021712,23.7414575574969),
	(22,23.7414575574969,27.4751761911214),
	(23,27.4751761911214,31.7689526197896),
	(24,31.7689526197896,36.7067955127581),
	(25,36.7067955127581,42.3853148396718),
	(26,42.3853148396718,48.9156120656225),
	(27,48.9156120656225,56.4254538754659),
	(28,56.4254538754659,65.0617719567858),
	(29,65.0617719567858,74.9935377503036),
	(30,74.9935377503036,86.4150684128492),
	(31,86.4150684128492,99.5498286747765),
	(32,99.5498286747765,114.654802975993),
	(33,114.654802975993,132.025523422392),
	(34,132.025523422392,152.001851935751),
	(35,152.001851935751,174.974629726113),
	(36,174.974629726113,201.39332418503),
	(37,201.39332418503,231.774822812785),
	(38,231.774822812785,266.713546234703),
	(39,266.713546234703,306.893078169908),
	(40,306.893078169908,353.099539895394),
	(41,353.099539895394,406.236970879703),
	(42,406.236970879703,467.345016511659),
	(43,467.345016511659,537.619268988407),
	(44,537.619268988407,618.434659336668),
	(45,618.434659336668,711.372358237169),
	(46,711.372358237169,818.250711972744),
	(47,818.250711972744,941.160818768655),
	(48,941.160818768655,1082.50744158395),
	(49,1082.50744158395,1245.05605782155),
	(50,1245.05605782155,1431.98696649478),
	(51,1431.98696649478,1646.95751146899),
	(52,1646.95751146899,1894.17363818934),
	(53,1894.17363818934,2178.47218391775),
	(54,2178.47218391775,2505.41551150541),
	(55,2505.41551150541,2881.40033823122),
	(56,2881.40033823122,3313.7828889659),
	(57,3313.7828889659,3811.02282231078),
	(58,3811.02282231078,4382.8487456574),
	(59,4382.8487456574,5040.44855750601),
	(60,5040.44855750601,5796.68834113191),
	(61,5796.68834113191,6666.3640923017),
	(62,6666.3640923017,7666.49120614695),
	(63,7666.49120614695,8816.637387069),
	(64,8816.637387069,10139.3054951293),
	(65,10139.3054951293,11660.3738193987),
	(66,11660.3738193987,13409.6023923086),
	(67,13409.6023923086,15421.2152511548),
	(68,15421.2152511548,17734.5700388281),
	(69,17734.5700388281,20394.9280446523),
	(70,20394.9280446523,23454.3397513501),
	(71,23454.3397513501,26972.6632140526),
	(72,26972.6632140526,31018.7351961605),
	(73,31018.7351961605,35671.7179755846),
	(74,35671.7179755846,41022.6481719223),
	(75,41022.6481719223,47176.2178977106),
	(76,47176.2178977106,54252.8230823672),
	(77,54252.8230823672,62390.9190447223),
	(78,62390.9190447223,71749.7294014306),
	(79,71749.7294014306,82512.3613116452),
	(80,82512.3613116452,94889.388008392),
	(81,94889.388008392,109122.968709651),
	(82,109122.968709651,125491.586516098),
	(83,125491.586516098,144315.496993513),
	(84,144315.496993513,165962.99404254),
	(85,165962.99404254,190857.615648921),
	(86,190857.615648921,219486.430496259),
	(87,219486.430496259,252409.567570698),
	(88,252409.567570698,290271.175206303),
	(89,290271.175206303,333812.023987248),
	(90,333812.023987248,383884.000085335),
	(91,383884.000085335,441466.772598136),
	(92,441466.772598136,507686.960987856),
	(93,507686.960987856,583840.177636034),
	(94,583840.177636034,671416.376781439),
	(95,671416.376781439,772129.005798655),
	(96,772129.005798655,887948.529168453),
	(97,887948.529168453,1021140.98104372),
	(98,1021140.98104372,1174312.30070028),
	(99,1174312.30070028,1350459.31830532),
	(100,1350459.31830532,1553028.38855112),
	(101,1553028.38855112,1785982.81933379),
	(102,1785982.81933379,2053880.41473386),
	(103,2053880.41473386,2361962.64944393),
	(104,2361962.64944393,2716257.21936052),
	(105,2716257.21936052,3123695.9747646),
	(106,3123695.9747646,3592250.54347929),
	(107,3592250.54347929,4131088.29750118),
	(108,4131088.29750118,4750751.71462636),
	(109,4750751.71462636,5463364.64432031),
	(110,5463364.64432031,6282869.51346836),
	(111,6282869.51346836,7225300.11298862),
	(112,7225300.11298862,8309095.30243691),
	(113,8309095.30243691,9555459.77030244),
	(114,9555459.77030244,10988778.9083478),
	(115,10988778.9083478,12637095.9171),
	(116,12637095.9171,14532660.477165),
	(117,14532660.477165,16712559.7212397),
	(118,16712559.7212397,19219443.8519257),
	(119,19219443.8519257,22102360.6022145),
	(120,22102360.6022145,25417714.8650467),
	(121,25417714.8650467,29230372.2673037),
	(122,29230372.2673037,33614928.2798993),
	(123,33614928.2798993,38657167.6943841),
	(124,38657167.6943841,44455743.0210418),
	(125,44455743.0210418,51124104.646698),
	(126,51124104.646698,58792720.5162027);
SET IDENTITY_INSERT [dbo].[DeviationPercent] OFF;
end